package database_layer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * A static class which is in charge of the functionality which doesn't involve
 * quering the database. It includes functions which initialize the DB,
 * retrieve connections to it, close connections, etc.
 * 
 * It's basically a utility class for the DB
 *
 */
public final class DatabaseManager
{
	/* Username and Password to access the DB */
	public static String username = "";
	public static String password = "";
	/* The location of the database */
	public static String connectionLink = "";
	/* The driver to be used */
	public static String driver = "";
	public static boolean inTestMode = false;
	
	/* The engine to be used */
	public static final String engine = "INNODB";
	public static final int allowedConnections = 5;
	public static ArrayList<Connection> connections = null;
	
	public static void initializeValues(String driver, String connectionLink,
			                            String username, String password,
			                            boolean inTestMode)
	{
		DatabaseManager.driver = driver;
		DatabaseManager.connectionLink = connectionLink;
		DatabaseManager.username = username;
		DatabaseManager.password = password;
		DatabaseManager.inTestMode = inTestMode;
	}
	
	public static void testPrint(String printString)
	{
		if (inTestMode)
		{
			System.out.println(printString);
		}
	}
	
	public static void performRollback(Connection con)
	{
		if (null != con)
		{
			try
			{
				con.rollback();
			}
			catch (SQLException e)
			{
				// Writing on the server console...
				System.out.println("Error while trying to rollback a transcation...");
			}
		}
	}
	
	public static Connection getConnection(int isolationLevel, boolean autoCommit) throws SQLException
	{
		Connection dbConnection = DriverManager.getConnection(connectionLink, username, password);
		dbConnection.setAutoCommit(autoCommit);
		dbConnection.setTransactionIsolation(isolationLevel);
		
		return dbConnection;
	}
	
	private static void initializeConnections()
	{
		connections = new ArrayList<Connection>();
		synchronized(connections)
		{
			for (int i = 0 ; i < allowedConnections ; i++)
			{
				try
				{
					Connection con = getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);
					connections.add(con);
				}
				catch (SQLException e)
				{
					System.out.println("Connections initialization error, closing connections...");	
					closeConnections();
					return;
				}
			}
		}
		
		System.out.println("Initialized all the connections");	
	}
	
	private static void closeConnections()
	{
		synchronized(connections)
		{
			while (0 != connections.size())
			{
				Connection conToClose = connections.remove(0);
				closeQuery(conToClose, null, null);				
			}
		}
		
		System.out.println("Closed all connections");
	}
	
	public static Connection getConnection()
	{
		Connection returnedConnection = null;
		synchronized(connections)
		{
			if (0 == connections.size())
			{
				return returnedConnection;
			}
			else
			{
				try
				{
					/* Getting the first connection */
					returnedConnection = connections.remove(0);
					if (returnedConnection.isClosed())
					{
						/* He's invalid? */
						returnedConnection = getConnection(Connection.TRANSACTION_REPEATABLE_READ, false);			
					}
				}
				catch (SQLException e)
				{
					System.out.println("Error when trying to retrieve a connection");
					return null;
				}
			}
		}
		
		return returnedConnection;
	}
	
	public static void returnConnection(Connection con)
	{
		if (null == con)
		{
			return;
		}
		
		synchronized(connections)
		{
			connections.add(con);
		}
	}
	
	public static void closeQuery(Connection dbConnection, Statement dbStatement,
			                      ResultSet dbResult)
	{
		try
		{
			if (null != dbResult)
			{
				dbResult.close();
			}
		}
		catch (SQLException e)
		{
			System.out.println("Error when trying to close a ResultSet");
		}
		
		try
		{
			if (null != dbStatement)
			{
				dbStatement.close();
			}
		}
		catch (SQLException e2)
		{
			System.out.println("Error when trying to close a Statement");
		}
		
		try
		{
			if (null != dbConnection)
			{
				dbConnection.close();
			}
		}
		catch (SQLException e3)
		{
			System.out.println("Error when trying to close a Connection");
		}	
	}
	
	public static void initDB() throws ClassNotFoundException, SQLException
	{
		testPrint("DB variables are: " +
				  driver + ", " +
                  connectionLink + ", " + 
                  username + ", " + 
                  password + ", " + 
                  inTestMode);
		
		Class.forName(driver);
		
		Statement dbStatement = null;
		Connection dbConnection = DriverManager.getConnection(connectionLink,
				                                              username,
				                                              password);
		try
		{
			dbConnection.setAutoCommit(false);
			dbStatement = dbConnection.createStatement();
			String usersTableQuery = "CREATE TABLE IF NOT EXISTS " + Common.usersTable + " (" +
					                 Common.usernameField + " VARCHAR(50) NOT NULL PRIMARY KEY, " +
					                 Common.passwordField + " VARCHAR(50) NOT NULL, "  +
					                 Common.firstNameField + " VARCHAR(50) NOT NULL, " +
					                 Common.lastNameField + " VARCHAR(50) NOT NULL, " +
					                 Common.phoneField + " VARCHAR(50) NOT NULL, " +
					                 Common.adminField + " BIT(1) NOT NULL, " +
					                 Common.addressField + " VARCHAR(100), " +
					                 Common.photoField + " BLOB) ENGINE=" + engine + ";";
			
			testPrint(usersTableQuery);
			dbStatement.executeUpdate(usersTableQuery);
			
			String coursesTableQuery = "CREATE TABLE IF NOT EXISTS " + Common.coursesTable + " (" +
									   Common.courseNameField + " VARCHAR(50) NOT NULL PRIMARY KEY, " +
					                   Common.descriptionField + " TEXT NOT NULL, " +
									   Common.creditPointsField + " FLOAT NOT NULL, " +
					                   Common.capacityField + " INT NOT NULL, " +
									   Common.alreadyRegisteredField + " INT NOT NULL, " +
									   Common.groupNumberField + " INT NOT NULL, " +
									   Common.courseCreatorField + " VARCHAR(50) NOT NULL) ENGINE=" + engine + ";";
			
			testPrint(coursesTableQuery);
			dbStatement.executeUpdate(coursesTableQuery);
			
			String sessionsTableQuery = "CREATE TABLE IF NOT EXISTS " + Common.sessionsTable + " (" +
									    Common.courseNameField + " VARCHAR(50) NOT NULL, " +
									    Common.dayField + " INT NOT NULL, " +
									    Common.startingTimeField + " INT NOT NULL, " +
									    Common.durationField + " INT NOT NULL, " +
									    Common.endingTimeField + " INT NOT NULL, " +
									    Common.groupNumberField + " INT NOT NULL, " +
									    "FOREIGN KEY (" + Common.courseNameField + ") REFERENCES " +
									    Common.coursesTable + "(" + Common.courseNameField + ") ON DELETE CASCADE) " +
									    "ENGINE=" + engine + ";";


			testPrint(sessionsTableQuery);
			dbStatement.executeUpdate(sessionsTableQuery);
			
			String registeredTableQuery = "CREATE TABLE IF NOT EXISTS " + Common.registeredTable + " (" +
										  Common.usernameField + " VARCHAR(50) NOT NULL, " +
										  Common.courseNameField + " VARCHAR(50) NOT NULL, " +
										  "FOREIGN KEY (" + Common.usernameField + ") REFERENCES " +
										  Common.usersTable + "(" + Common.usernameField + ") ON DELETE CASCADE, " +
										  "FOREIGN KEY (" + Common.courseNameField + ") REFERENCES " +
										  Common.coursesTable + "(" + Common.courseNameField + ") ON DELETE CASCADE) " +
										  "ENGINE=" + engine + ";";

			testPrint(registeredTableQuery);
			dbStatement.executeUpdate(registeredTableQuery);
			
			dbConnection.commit();
		}
		catch (SQLException e)
		{
			dbConnection.rollback();
			System.out.println("Got an exception when trying to initialize the DB");
			throw new SQLException();
		}
		finally
		{
			closeQuery(dbConnection, dbStatement, null);
		}
		initializeConnections();
	}
	
	public static void destroyDB() throws Exception
	{
		closeConnections();
	}
}
